Object permissions should not be assigned to PUBLIC or GUEST.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15172	DM6196-SQLServer9	SV-23871r1_rule	ECLP-1	Medium

Description
The guest account is available to users that do not have authorized accounts on the database. The PUBLIC role is granted to all users of the database regardless of assigned job function. Assignment of object privileges to unauthorized users can compromise data integrity and/or confidentiality.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-04-03

Details

Check Text ( None )
None

Fix Text (F-19722r1_fix)
Revoke any unauthorized object privileges assigned to PUBLIC or GUEST where supported by the DBMS vendor. Document all remaining object privileges assigned to PUBLIC in the System Security Plan and authorize with the IAO. From the query prompt: SELECT name FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [database name] REVOKE [privilege] ON [object name] FROM '[public or guest]' Repeat for each object privilege assigned to public or guest: From the query prompt: USE [database name] REVOKE [permission] ON [schema name].[object name] TO PUBLIC To determine correct schema name for the object, use: SELECT SCHEMA_NAME(schema_id) FROM [master].sys.all_objects WHERE name = '[object name]'

Fix Text (F-19722r1_fix)

Revoke any unauthorized object privileges assigned to PUBLIC or GUEST where supported by the DBMS vendor.

Document all remaining object privileges assigned to PUBLIC in the System Security Plan and authorize with the IAO.

From the query prompt:

SELECT name
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [database name]
REVOKE [privilege] ON [object name] FROM '[public or guest]'

Repeat for each object privilege assigned to public or guest:

From the query prompt:

USE [database name]
REVOKE [permission] ON [schema name].[object name] TO PUBLIC

To determine correct schema name for the object, use:

SELECT SCHEMA_NAME(schema_id)
FROM [master].sys.all_objects
WHERE name = '[object name]'